from database_manager import SQLiteDBManager
import config as cfg
import pandas as pd
import datetime

now = datetime.datetime.now()
hour = now.hour + now.minute / 60
today=datetime.datetime.today()
fnow_time=now.strftime("%Y-%m-%d %H:%M:%S")

 # 编写SQL查询语句
if hour<8.5:#日期，班次
    today=today-datetime.timedelta(days=1)
    date1=today.strftime("%Y-%#m-%d")
    shift="N"
elif hour>20.5:
    date1=today.strftime("%Y-%#m-%d")
    shift="N"
else:
    date1=today.strftime("%Y-%#m-%d")
    shift="D"
print(date1,shift)
now_times=today.strftime("%Y%m%d")

# 定义计算 avg_eta 的函数
def calculate_avg_eta(group):
    return (group['sum_IvGrade'] * group['eta1']).sum() / group['sum_IvGrade'].sum()

# 定义计算入库效率的函数
def calculate_ruku_avg(group):
    class_values = group['class'].str.replace('Eta', '').astype(float)
    return (class_values * group['sum_IvGrade']).sum() / group['sum_IvGrade'].sum()
db_manager = SQLiteDBManager(cfg.db_path)
if db_manager.connect():
    try:
        # 1.更新开班对切 DataFrame
        df = pd.read_sql_query(cfg.getdb_all_sql, db_manager.conn)
        df["cutover_completed"]=0
        df["opening_isc"]=0
        df["opening_uoc"]=0
        df["opening_ff"]=0
        df["current_isc"]=0
        df["current_uoc"]=0
        df["current_ff"]=0
        df["efficiency_impact"]=0
        df.to_sql('ip_config', db_manager.conn, if_exists='replace', index=False)

        # # 2.更新车间效率 DataFrame
        # kaiban_query = f"SELECT * FROM GearData1 WHERE date = '{now_times}' AND shift = '{shift}' AND class LIKE '%Eta%' AND class NOT LIKE '%Eta Fail%'"
        # kaiban_query1 = f"SELECT BinFileName,Class,sum(sum_IvGrade) FROM GearData1 WHERE date = '{now_times}' AND shift = '{shift}' group by BinFileName,Class"
        # kaiban_query_dlcc = f"SELECT BinFileName,quantity as dlcc FROM sumbin WHERE date = '{now_times}' AND shift = '{shift}' group by BinFileName and bin=7"
        # etadf = pd.read_sql_query(kaiban_query, db_manager.conn)
        # etadf1 = pd.read_sql_query(kaiban_query1, db_manager.conn)
        # etadf_dlcc = pd.read_sql_query(kaiban_query_dlcc, db_manager.conn)
        # etadf_dlcc['shift'] = shift
        # etadf_dlcc['date']=now_times
        # # 对 df 进行聚合操作
        # etadf = etadf.groupby('BinFileName').agg({
        #     'eta1':lambda x: (x * etadf.loc[x.index, 'sum_IvGrade']).sum() / etadf.loc[x.index, 'sum_IvGrade'].sum(),
        #     'Aoi1R':lambda x: (etadf.loc[x.index,'Class'].str.replace('Eta', '').str.rstrip('%').astype(float) * etadf.loc[x.index,'sum_IvGrade']).sum() / etadf.loc[x.index,'sum_IvGrade'].sum()
        # }).rename(columns={'eta1': 'avg_eta','Aoi1R':'ru_eta'}).reset_index()
        # etadf['shift'] = shift
        # etadf['date']=now_times
        # print(etadf)
        # # 所有数据
        # sumetadf = etadf1.groupby('BinFileName').agg({'sum(sum_IvGrade)': 'sum'}).reset_index()
        # sumetadf = sumetadf.rename(columns={'sum(sum_IvGrade)': 'sum_IvGrade'})
        # all_bin_files = etadf1['BinFileName'].unique()
        # # 漏电合并档
        # hbd_ireb = etadf1[etadf1['Class']=='IRev2-B'].groupby('BinFileName').sum().reset_index()
        # hbd_ireb = hbd_ireb.reindex(columns=['BinFileName','sum(sum_IvGrade)']).set_index('BinFileName').reindex(all_bin_files, fill_value=0).reset_index()
        # hbd_ireb = hbd_ireb.rename(columns={'sum(sum_IvGrade)': 'IRev2-B'})
        # # 漏电NG
        # hbd_irefail = etadf1[etadf1['Class']=='IRev2 Fail'].groupby('BinFileName').sum().reset_index()
        # hbd_irefail = hbd_irefail.reindex(columns=['BinFileName','sum(sum_IvGrade)']).set_index('BinFileName').reindex(all_bin_files, fill_value=0).reset_index()
        # hbd_irefail = hbd_irefail.rename(columns={'sum(sum_IvGrade)': 'IRev2 Fail'})

        # # 合并档其他
        # qthbd = pd.concat([
        #     etadf1[etadf1['Class']=='IRev2-B'],
        #     etadf1[etadf1['Class']=='UNG'],
        #     etadf1[etadf1['Class']=='Rsh-B']
        # ])
        # qthbd=qthbd.rename(columns={'sum(sum_IvGrade)': 'qthbd'})

        # # 合并档位
        # hbddw = etadf1[etadf1['Class'].str.contains('Eta') & ~etadf1['Class'].str.contains('Eta Fail')].sort_values(by='Class').groupby('BinFileName').first()
        # hbddw = hbddw.rename(columns={'sum(sum_IvGrade)': 'dwhbd'})
        # hbd = pd.merge(qthbd, hbddw, on='BinFileName')
        # hbd['hbd'] = hbd['qthbd'] + hbd['dwhbd']
        # hbd = hbd[['BinFileName', 'hbd']]

        # # 合并所有
        # all_df=pd.merge(etadf,hbd,on='BinFileName',how='outer')
        # all_df=pd.merge(all_df,sumetadf,on='BinFileName',how='outer')
        # all_df=pd.merge(all_df,hbd_ireb,on='BinFileName',how='outer')
        # all_df=pd.merge(all_df,hbd_irefail,on='BinFileName',how='outer')
        # all_df=pd.merge(all_df,etadf_dlcc,on='BinFileName',how='outer')


        # print(all_df)
        # delete_query = f"DELETE FROM alldata WHERE date = '{now_times}' AND shift = '{shift}'"
        # if db_manager.conn is not None:
        #     try:
        #         db_manager.conn.execute(delete_query)
        #         # db_manager.conn.execute(update_time_query)
        #         db_manager.conn.commit()
        #     except Exception as e:
        #         print(f"提交事务时发生错误: {e}")
        #         print("已删除指定日期和班次的数据")
        # all_df.to_sql('alldata', db_manager.conn, if_exists='replace', index=False)
    except Exception as e:
        print(f"发生错误: {e}")
    finally:
        # 关闭数据库连接
        db_manager.close()